// GROUP:		P. Edwards, K. O'Keefe, B. Weaver, Z. Wheeler
// CLASS:		COMP 244, Database Management Systems
// DESCRIPTION:	Final Project Code

#include <iostream>
#include <iomanip>
#include <vector>
#include <string.h>
#include <sstream>
#include <Windows.h>
#undef UNICODE
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h> //Microsoft specific header file for using ODBC
using namespace std;


struct StanzaInfo
{
	short stanza_type;
	short stanza_nr;
};
struct Song
{
	int id;
	string title;
	vector<vector<string>> stanzas;
	vector<StanzaInfo> stanzasinfo;
	string comment;
	int versecount, choruscount, bridgecount, misccount;
};
struct Presentation
{
	string title;
	string author;
	vector<Song> songs;
	string comment;
	SQL_DATE_STRUCT dateCreatedFor;
};


void setup(SQLHENV&, SQLRETURN&, SQLHDBC&, SQLHSTMT&); //prepares through setting the database to company
void allocate_handle(SQLRETURN&, SQLHDBC&, SQLHSTMT&);
void deallocate_handle(SQLRETURN&, SQLHDBC&, SQLHSTMT&);
void Create_Song(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Song&, int);
void Create_Presentation(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Presentation&, int);
void GetSongTitles(SQLRETURN&, SQLHDBC&, SQLHSTMT&, vector<string>&);
void ChooseSongForStanza(SQLRETURN&, SQLHDBC&, SQLHSTMT&, int, Song&);
void ChooseStanzaFromSong(SQLRETURN&, SQLHDBC&, SQLHSTMT&, string, int, Song&);
void GetStanzas(SQLRETURN&, SQLHDBC&, SQLHSTMT&, string, vector<vector<string>>&);
void GetSongID(SQLRETURN&, SQLHDBC&, SQLHSTMT&, string, int&);
void GetStanzaInfo(SQLRETURN&, SQLHDBC&, SQLHSTMT&, int, vector<StanzaInfo>&);
void GetLines(SQLRETURN&, SQLHDBC&, SQLHSTMT&, int, StanzaInfo, vector<string>&);
void ViewEditSong(Song&);
void CreateStanza(Song&, int);
void ChooseSong(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Presentation&, int);
void ViewEditPresentation(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Presentation&, int);
void GetStanzaInfoForStanza(SQLRETURN&, SQLHDBC&, SQLHSTMT&, int, int, StanzaInfo&);
void ChooseStanzaType(StanzaInfo&, int);
void EnterComment(string&);
void InsertSong(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Song&);
void InsertIntoSong(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Song);
void InsertIntoStanza(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Song, int);
void InsertIntoStanza_Has_Line(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Song, int);
void InsertIntoSong_Has_Stanza(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Song, int);
void InsertPresentation(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Presentation&);
void ChooseDateCreatedFor(Presentation&, int);
void InsertIntoPresentation(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Presentation);
void InsertIntoPresentation_Has_Song(SQLRETURN&, SQLHDBC&, SQLHSTMT&, Presentation);
void DeletePresentation(SQLRETURN&, SQLHDBC&, SQLHSTMT&, int);
void GetPresentationTitles(SQLRETURN&, SQLHDBC&, SQLHSTMT&, vector<string>&);
void GetPresTitles(SQLRETURN&, SQLHDBC&, SQLHSTMT&, string, vector<string>&);
void GetPresentationAuthors(SQLRETURN&, SQLHDBC&, SQLHSTMT&, string, vector<string>&);
void GetPresAuthors(SQLRETURN&, SQLHDBC&, SQLHSTMT&, vector<string>&);
void DeleteFromPresentation(SQLRETURN&, SQLHDBC&, SQLHSTMT&, string, string);
void DeleteFromPresentation_Has_Song(SQLRETURN&, SQLHDBC&, SQLHSTMT&, string, string);



inline void gotoxy(int x, int y)
{
	COORD coord;
	coord.X = x; 
	coord.Y = y;
	SetConsoleCursorPosition(GetStdHandle(STD_OUTPUT_HANDLE),
							coord);
}
inline void ClearConsoleLines(int start, int end)
{
	gotoxy(0,start);
	for(start; start<=end; ++start)
	{
		for(int i=0; i<80; ++i)
			cout << " ";
	}
}
inline void changeColor(int color)
{
	SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), color);
}



int main ()
{
	ShowWindow( GetConsoleWindow() , SW_MAXIMIZE);
	SQLHENV henv1; //SQL environment handle
	SQLRETURN retvalue;
	SQLHDBC hdbc1;
	SQLHSTMT hstmt1;

	setup(henv1, retvalue, hdbc1, hstmt1);
	

	while(true)
	{
		system("cls");
		
		unsigned int choice = 0, selectColor=240, oldchoice=3;
		gotoxy(30,0);
		cout << "Would you like to:";
		while(!GetAsyncKeyState(VK_RETURN))
		{
			if(choice!=oldchoice)
			{
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(5, 3);
				cout << "Create a Presentation";
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(32, 3);
				cout << "Create a Song";
				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
				if(choice==2)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(60, 3);
				cout << "Delete Presentation";
				if(choice==2)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
				if(choice==3)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(38,5);
				cout << "Exit";
				if(choice==3)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
				while(GetAsyncKeyState(VK_LEFT) || GetAsyncKeyState(VK_RIGHT))
				;
			}

			oldchoice=choice;
			if(GetAsyncKeyState(VK_RIGHT))
				choice=(oldchoice+1)%4;
			if(GetAsyncKeyState(VK_LEFT))
			{
				if(choice==0)
					choice = 3;
				else
					--choice; 
			}
		}
		while(GetAsyncKeyState(VK_RETURN))
			;
		FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));

		if(choice==0)
		{
			Presentation newpresentation;
			Create_Presentation(retvalue, hdbc1, hstmt1, newpresentation, selectColor);
		}

		if(choice==1)
		{
			Song newsong;
			newsong.versecount=0;
			newsong.choruscount=0;
			newsong.bridgecount=0;
			newsong.misccount=0;
			Create_Song(retvalue, hdbc1, hstmt1, newsong, selectColor);
		}

		if(choice==2)
		{
			DeletePresentation(retvalue, hdbc1, hstmt1, selectColor);
		}
		
		if(choice==3)
			return 0;
	}
	
}


void setup(SQLHENV& henv1, SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{

	retvalue = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv1);

	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to allocate environment handle" << endl;
		exit(1);
	}

	retvalue = SQLSetEnvAttr(henv1, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to set environment attribute" << endl;
		exit(1);
	}

	

	retvalue = SQLAllocHandle(SQL_HANDLE_DBC, henv1, &hdbc1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to allocate database connection handle" << endl;
		exit(1);
	}

	retvalue = SQLConnect(hdbc1, (SQLCHAR*)"COMP244_ODBC", SQL_NTS, NULL, 0, NULL, 0);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to connect to the data source" << endl;
		exit(1);
	}

	
	retvalue = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to allocate statement handle" << endl;
		exit(1);
	}

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)"Use song_database", SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"Use song_database\"" << endl;
		exit(1);
	}

	retvalue = SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to free hsmt1" << endl;
		exit(1);
	}


}

void allocate_handle(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{

	retvalue = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to allocate statement handle" << endl;
		exit(1);
	}

}

void deallocate_handle(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{
	retvalue = SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to free hsmt1" << endl;
		exit(1);
	}

}

void Create_Song(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Song& song, int selectColor)
{
	
	vector<string> titles;
	GetSongTitles(retvalue, hdbc1, hstmt1, titles);

	system("cls");
	bool bad;
	stringstream ss;
	do
	{
		bad=false;
		cout << "What would you like to call your song?(80 character max)\n\t";
		char temp[81];
		cin.getline(temp,80);
		cin.sync();
		ss << temp;
		song.title=ss.str();
		ss.str(string());
		ss.clear();
		while(GetAsyncKeyState(VK_RETURN))
			;
		for(unsigned int i = 0; i < titles.size(); ++i)
		{
			if(song.title==titles[i])
			{
				system("cls");
				cout << "I'm sorry, the song name \"" << song.title << "\" already exists. Please try again.\n";
				bad = true;
				break;
			}
		}

	}while(bad);

	int choice, oldchoice;

	do
	{
		system("cls");
		choice = 0;
		oldchoice=1;
		gotoxy(30,0);
		cout << "Would you like to:";
		do
		{
			if(choice!=oldchoice)
			{
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(5, 3);
				cout << "Add an Existing Stanza";
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(32, 3);
				cout << "Create a Stanza";
				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

				if(choice==2)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(57, 3);
				cout << "View/Edit Song";
				if(choice==2)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

				if(choice==3)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE),selectColor);
				gotoxy(35, 5);
				cout << "Finish";
				if(choice==3)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

				while(GetAsyncKeyState(VK_LEFT) || GetAsyncKeyState(VK_RIGHT))
				;
			}
			oldchoice=choice;
			if(GetAsyncKeyState(VK_RIGHT))
				choice=(oldchoice+1)%4;
			if(GetAsyncKeyState(VK_LEFT))
			{
				choice = (oldchoice-1);
				if(choice<0)
					choice = 3;
				else 
					choice%=4;
			}
		}while(!GetAsyncKeyState(VK_RETURN));
		while(GetAsyncKeyState(VK_RETURN))
			;
		FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));

		if(choice==0)
		{
			ChooseSongForStanza(retvalue, hdbc1, hstmt1, selectColor, song);
		}

		if(choice==1)
		{
			CreateStanza(song, selectColor);
		}

		if(choice==2)
		{
			ViewEditSong(song);
		}
		
		if(choice==3)
		{
			if(!song.stanzas.empty())
			{
				EnterComment(song.comment);
				InsertSong(retvalue, hdbc1, hstmt1, song);
			}
		}
	}while(choice!=3);

}

void Create_Presentation(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Presentation& presentation, int selectColor)
{

	system("cls");
	stringstream ss;
	cout << "Who is the author?(20 character max)\n\t";
	char tempa[41];
	cin.getline(tempa, 40);
	cin.sync();
	ss << tempa;
	presentation.author = ss.str();
	ss.str(string());
	ss.clear();
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));

	vector<string> titles, authors;
	GetPresAuthors(retvalue, hdbc1, hstmt1, authors);
	bool authexist = false, presexist=false;
	for(int i = 0; i < authors.size(); ++i)
	{
		if(presentation.author==authors[i])
		{
			authexist=true;
			break;
		}
	}

	if(authexist)
	{
		GetPresTitles(retvalue, hdbc1, hstmt1, presentation.author, titles);
	}

	do
	{
		system("cls");
		cout << "What would you like to call your presentation?(20 character max)\n\t";
		char tempt[21];
		cin.getline(tempt,20);
		cin.sync();
		ss << tempt;
		presentation.title = ss.str();
		ss.str(string());
		ss.clear();
		while(GetAsyncKeyState(VK_RETURN))
			;
		FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));

		presexist=false;

		if(authexist)
		{
			for(int i = 0; i < titles.size(); ++i)
			{
				if(presentation.title==titles[i])
				{
					presexist=true;
					break;
				}
			}

			if(presexist)
			{
				system("cls");
				cout << "I'm sorry, a presentation with that author and that name already exist.\n"
					<<"Press RETURN to enter a presentation name.";
				while(!GetAsyncKeyState(VK_RETURN))
					;
				while(GetAsyncKeyState(VK_RETURN))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}
		}
	}while(presexist);

	

	unsigned int oldchoice, choice;
	do
	{
		system("cls");
		string str = "Would you like to:";
		gotoxy((80-str.length())/2, 0);
		cout << str;
		gotoxy(5,2);
		SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
		cout << "Add a Song";
		SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		gotoxy(29, 2);
		cout << "View/Edit Presentation";
		gotoxy(62,2);
		cout << "Finish";
		choice=0;
		oldchoice=1;
		do
		{
			
			if(oldchoice!=choice)
			{
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(5,2);
				cout << "Add a Song";
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(29, 2);
				cout << "View/Edit Presentation";
				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

				if(choice==2)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(62,2);
				cout << "Finish";
				if(choice==2)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
			}
			oldchoice=choice;

			if(GetAsyncKeyState(VK_RIGHT))
			{
				choice = (choice+1)%3;
				while(GetAsyncKeyState(VK_RIGHT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(VK_LEFT))
			{
				if(choice==0)
					choice=2;
				else
					--choice;
				while(GetAsyncKeyState(VK_LEFT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

		}while(!GetAsyncKeyState(VK_RETURN));
		while(GetAsyncKeyState(VK_RETURN))
			;
		FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));

		if(choice==0)
			ChooseSong(retvalue, hdbc1, hstmt1, presentation, selectColor);
		
		if(choice==1)
			ViewEditPresentation(retvalue, hdbc1, hstmt1, presentation, selectColor);
		if(choice==2)
		{
			if(!presentation.songs.empty())
			{
				EnterComment(presentation.comment);
				ChooseDateCreatedFor(presentation, selectColor);
				InsertPresentation(retvalue, hdbc1, hstmt1, presentation);
			}
		}


	}while(choice!=2);

}

void GetSongTitles(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, vector<string>& titles)
{
	SQLINTEGER name_length;
	SQLCHAR title[81];
	stringstream ss;

	allocate_handle(retvalue, hdbc1, hstmt1);

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)"SELECT song_title FROM song ORDER BY song_title ASC", SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"Option B\"" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &title, sizeof(title), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		ss << title;
		titles.push_back(ss.str());
		ss.str(string());
		ss.clear();
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);

}

void ChooseSongForStanza(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, int selectColor, Song& song)
{

	vector<string> titles;
	GetSongTitles(retvalue, hdbc1, hstmt1, titles);

	system("cls");
	cout << "Select the song from which you would like to choose a stanza."
		<< "\n\tUP and DOWN for selecting; LEFT and RIGHT for fast scrolling";
	unsigned int choice = 0, oldchoice=0, level=0, oldlevel=0;
	for(unsigned int i=0; i<14 && i<titles.size(); ++i)
	{
		gotoxy(0,4+4*i);
		if(i==0)
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
		cout << titles[i];
		if(i==0)
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
	}
	do
	{
		
		if(choice!=oldchoice || oldlevel!=level)
		{
			if(oldlevel==level)
			{
				gotoxy(0, 4+4*oldchoice);
				cout << titles[oldchoice+level*14];
			}
			else
			{
				for(unsigned int i=0; i<14; ++i)
				{
					gotoxy(0, 4+4*i);
					for(unsigned int j=0; j<80; ++j)
						cout << " ";
				}
				for(unsigned int i=0; i<14 && i+level*14<titles.size(); ++i)
				{
					gotoxy(0,4+4*i);
					cout << titles[i+level*14];
				}
			}
			gotoxy(0, 4+4*choice);
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << titles[choice+level*14];
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		}
		oldchoice=choice;
		oldlevel=level;

		if(GetAsyncKeyState(VK_DOWN))
		{
			if(choice == 13)
			{
				if((level+1)*14 < titles.size())
				{
					++level;
					choice=0;
				}
				else 
					choice=13;
			}
			else if(choice+level*14==titles.size()-1)
				;
			else
				++choice;
			while(GetAsyncKeyState(VK_DOWN))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}
		
		if(GetAsyncKeyState(VK_UP))
		{
			if(choice==0)
			{
				if(level==0)
					;
				else
				{
					--level;
					choice=13;
				}
			}
			else 
				--choice;
			while(GetAsyncKeyState(VK_UP))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_RIGHT))
		{
			if((level+1)*14<titles.size())
			{
				++level;
				choice=0;
			}
			while(GetAsyncKeyState(VK_RIGHT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_LEFT))
		{
			if(level>0)
			{
				--level;
				choice=13;
			}
			else
				;
			while(GetAsyncKeyState(VK_LEFT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}



	}while(!GetAsyncKeyState(VK_RETURN));
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));


	ChooseStanzaFromSong(retvalue, hdbc1, hstmt1, titles[choice+level*14], selectColor, song);
}

void ChooseStanzaFromSong(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, string title, int selectColor, Song& song)
{
	vector<vector<string>> stanzas;
	GetStanzas(retvalue, hdbc1, hstmt1, title, stanzas);
	int id;
	GetSongID(retvalue, hdbc1, hstmt1, title, id);
	system("cls");
	stringstream ss;
	cout << "Select the stanza you would like to add.\n(RIGHT and LEFT change stanzas and RETURN selects the current stanza)";
	ss << "Stanza " << 1 << " of " << stanzas.size();
	string str = ss.str();
	ss.str(string());
	ss.clear();
	gotoxy((80-str.length())/2, 3);
	cout << str;
	for(unsigned int j = 0; j<stanzas[0].size(); ++j)
	{
		gotoxy((80-stanzas[0][j].length())/2, 7+j);
		cout << stanzas[0][j];
	}

	unsigned int choice = 0, oldchoice=0;
	do
	{
		
		if(choice!=oldchoice)
		{
			system("cls");
			cout << "Select the stanza you would like to add.\n(RIGHT and LEFT change stanzas and RETURN selects the current stanza)";
			ss << "Stanza " << choice+1 << " of " << stanzas.size();
			string str = ss.str();
			ss.str(string());
			ss.clear();
			gotoxy((80-str.length())/2, 3);
			cout << str;
			for(unsigned int i = 0; i < stanzas[choice].size(); ++i)
			{
				gotoxy((80-stanzas[choice][i].length())/2, 7+i);
				cout << stanzas[choice][i];
			}
		}
		oldchoice=choice;

		if(GetAsyncKeyState(VK_RIGHT))
		{
			if(choice==stanzas.size()-1)
				choice=0;
			else
				++choice;
			while(GetAsyncKeyState(VK_RIGHT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_LEFT))
		{
			if(choice==0)
				choice=stanzas.size()-1;
			else
				--choice;
			while(GetAsyncKeyState(VK_LEFT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}



	}while(!GetAsyncKeyState(VK_RETURN));
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
	
	song.stanzas.push_back(stanzas[choice]);
	StanzaInfo info;
	GetStanzaInfoForStanza(retvalue, hdbc1, hstmt1, id, choice+1, info);
	if(info.stanza_type==1)
	{
		info.stanza_nr = song.versecount+1;
		++song.versecount;
	}
	if(info.stanza_type==2)
	{
		info.stanza_nr = song.choruscount+1;
		++song.choruscount;
	}
	if(info.stanza_type==3)
	{
		info.stanza_nr = song.bridgecount+1;
		++song.bridgecount;
	}
	if(info.stanza_type==4)
	{
		info.stanza_nr = song.misccount+1;
		++song.misccount;
	}
	song.stanzasinfo.push_back(info);

}

void GetStanzas(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, string title, vector<vector<string>>& stanzas)
{
	int songid;
	GetSongID(retvalue, hdbc1, hstmt1, title, songid);
	vector<StanzaInfo> stanzasinfo;
	GetStanzaInfo(retvalue, hdbc1, hstmt1, songid, stanzasinfo);
	for(unsigned int i = 0; i<stanzasinfo.size(); ++i)
	{
		vector<string> temp;
		stanzas.push_back(temp);
		GetLines(retvalue, hdbc1, hstmt1, songid, stanzasinfo[i], stanzas[i]);
	}

	
}

void GetSongID(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, string title, int& songid)
{
	stringstream ss;
	const char* temp = title.c_str();
	for(int i = 0; i<title.length(); ++i)
	{
		ss << temp[i];
		if(temp[i]=='\'')
			ss << '\'';
	}
	string titl;
	titl=ss.str();
	ss.str(string());
	ss.clear();
	ss << "SELECT song_id FROM song WHERE song_title='" << titl << "'";
	allocate_handle(retvalue, hdbc1, hstmt1);

	SQLINTEGER id, name_length;

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"GetSongID\"" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_LONG, &id, sizeof(id), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		songid=id;
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void GetStanzaInfo(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, int songid, vector<StanzaInfo>& stanzasinfo)
{
	stringstream ss;
	ss << "SELECT stanza_type, stanza_nr FROM song_has_stanza WHERE song_id = " << songid << " ORDER BY index_in_song ASC";
	allocate_handle(retvalue, hdbc1, hstmt1);

	SQLINTEGER name_length;
	SQLSMALLINT stanza_type, stanza_nr;

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"GetIndexInSong\"" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_SHORT, &stanza_type, sizeof(stanza_type), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 2, SQL_C_SHORT, &stanza_nr, sizeof(stanza_nr), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 2" << endl;
		exit(1);
	}

	StanzaInfo st;

	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		st.stanza_type = stanza_type;
		st.stanza_nr = stanza_nr;
		stanzasinfo.push_back(st);
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void GetLines(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, int songid, StanzaInfo stanzaInfo, vector<string>& lines)
{
	stringstream ss;
	ss << "SELECT line_text FROM stanza_has_line WHERE song_id=" << songid << " AND stanza_type="
		<< stanzaInfo.stanza_type << " AND stanza_nr=" << stanzaInfo.stanza_nr << " ORDER BY index_in_stanza ASC";

	allocate_handle(retvalue, hdbc1, hstmt1);

	SQLINTEGER name_length;
	SQLCHAR line[101];

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"GetLines\"" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &line, sizeof(line), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}



	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		ss.str(string());
		ss.clear();
		ss << line;
		lines.push_back(ss.str());
	}


	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void ViewEditSong(Song& song)
{
	system("cls");
	if(!song.stanzas.empty())
	{
		stringstream ss;
		cout << "Press RETURN to go back or press X to delete the current stanza";
		ss << "Stanza " << 1 << " of " << song.stanzas.size();
		string str = ss.str();
		ss.str(string());
		ss.clear();
		gotoxy((80-str.length())/2, 2);
		cout << str;
		for(unsigned int j = 0; j<song.stanzas[0].size(); ++j)
		{
			gotoxy((80-song.stanzas[0][j].length())/2, 6+j);
			cout << song.stanzas[0][j];
		}

		unsigned int choice = 0, oldchoice=0;
		bool erase = false;
		do
		{
		
			if(choice!=oldchoice || erase)
			{
				system("cls");
				cout << "Press RETURN to go back or press X to delete the current stanza";
				ss << "Stanza " << choice+1 << " of " << song.stanzas.size();
				string str = ss.str();
				ss.str(string());
				ss.clear();
				gotoxy((80-str.length())/2, 2);
				cout << str;
				for(unsigned int i = 0; i < song.stanzas[choice].size(); ++i)
				{
					gotoxy((80-song.stanzas[choice][i].length())/2, 6+i);
					cout << song.stanzas[choice][i];
				}
			}
			oldchoice=choice;
			erase=false;

			if(GetAsyncKeyState(VK_RIGHT))
			{
				if(choice==song.stanzas.size()-1)
					choice=0;
				else
					++choice;
				while(GetAsyncKeyState(VK_RIGHT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(VK_LEFT))
			{
				if(choice==0)
					choice=song.stanzas.size()-1;
				else
					--choice;
				while(GetAsyncKeyState(VK_LEFT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}
			if(GetAsyncKeyState(0x58))
			{
				song.stanzas.erase(song.stanzas.begin()+choice);
				song.stanzasinfo.erase(song.stanzasinfo.begin()+choice);
				if(choice==song.stanzas.size() && choice==0)
					break;
				if(choice==song.stanzas.size())
					--choice;
				erase=true;
				while(GetAsyncKeyState(0x58))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(choice<0)
				break;


		}while(!GetAsyncKeyState(VK_RETURN));
	}
	if(song.stanzas.empty())
	{
		system("cls");
		cout << "I'm sorry, this song contains no stanzas.\n"
			<< "Press RETURN to go back.";
		while(!GetAsyncKeyState(VK_RETURN))
			;
	}
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
}

void CreateStanza(Song& song, int selectColor)
{
	system("cls");
	vector<string> lines;
	cout << "Enter the first line of the stanza and then press RETURN. (100 character max)\n";
	char temp[101];
	cin.getline(temp,100);
	cin.sync();
	lines.push_back(temp);
	unsigned int oldchoice, choice;
	system("cls");
	gotoxy((80-lines[0].length())/2, 5);
	cout << lines[0];
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
	do
	{
		string str = "Would you like to:";
		gotoxy((80-str.length())/2, 0);
		cout << str;
		gotoxy(15,2);
		SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
		cout << "Add a line";
		SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		gotoxy(50, 2);
		cout << "Finish";
		choice=0;
		oldchoice=1;
		do
		{
			
			if(oldchoice!=choice)
			{
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(15,2);
				cout << "Add a line";
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(50,2);
				cout << "Finish";
				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
			}
			oldchoice=choice;

			if(GetAsyncKeyState(VK_RIGHT))
			{
				choice = (choice+1)%2;
				while(GetAsyncKeyState(VK_RIGHT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(VK_LEFT))
			{
				choice = (choice+1)%2;
				while(GetAsyncKeyState(VK_LEFT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

		}while(!GetAsyncKeyState(VK_RETURN));
		while(GetAsyncKeyState(VK_RETURN))
			;
		FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));

		if(choice==0)
		{
			ClearConsoleLines(0, 2);
			gotoxy(0,0);
			cout << "Please enter the line you would like to add. (100 char max)\n";
			cin.getline(temp, 100);
			cin.sync();
			lines.push_back(temp);
			gotoxy((80-lines[lines.size()-1].length())/2, 5+lines.size()-1);
			cout << lines[lines.size()-1];

		}

		if(choice==1)
		{
			StanzaInfo info;
			ChooseStanzaType(info, selectColor);
			if(info.stanza_type==1)
			{
				info.stanza_nr = song.versecount+1;
				++song.versecount;
			}
			if(info.stanza_type==2)
			{
				info.stanza_nr = song.choruscount+1;
				++song.choruscount;
			}
			if(info.stanza_type==3)
			{
				info.stanza_nr = song.bridgecount+1;
				++song.bridgecount;
			}
			if(info.stanza_type==4)
			{
				info.stanza_nr = song.misccount+1;
				++song.misccount;
			}
			song.stanzasinfo.push_back(info);
		}
		while(GetAsyncKeyState(VK_RETURN))
				;
		FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		ClearConsoleLines(0,1);


	}while(choice!=1);

	song.stanzas.push_back(lines);

}

void ChooseSong(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Presentation& presentation, int selectColor)
{
	vector<string> titles;
	GetSongTitles(retvalue, hdbc1, hstmt1, titles);

	system("cls");
	cout << "Select the song which you would like to add to the presentation."
		<< "\n\tUP and DOWN for selecting; LEFT and RIGHT for fast scrolling";
	unsigned int choice = 0, oldchoice=0, level=0, oldlevel=0;
	for(unsigned int i=0; i<14 && i<titles.size(); ++i)
	{
		gotoxy(0,4+4*i);
		if(i==0)
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
		cout << titles[i];
		if(i==0)
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
	}
	do
	{
		
		if(choice!=oldchoice || oldlevel!=level)
		{
			if(oldlevel==level)
			{
				gotoxy(0, 4+4*oldchoice);
				cout << titles[oldchoice+level*14];
			}
			else
			{
				for(unsigned int i=0; i<14; ++i)
				{
					gotoxy(0, 4+4*i);
					for(unsigned int j=0; j<80; ++j)
						cout << " ";
				}
				for(unsigned int i=0; i<14 && i+level*14<titles.size(); ++i)
				{
					gotoxy(0,4+4*i);
					cout << titles[i+level*14];
				}
			}
			gotoxy(0, 4+4*choice);
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << titles[choice+level*14];
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		}
		oldchoice=choice;
		oldlevel=level;

		if(GetAsyncKeyState(VK_DOWN))
		{
			if(choice == 13)
			{
				if((level+1)*14 < titles.size())
				{
					++level;
					choice=0;
				}
				else 
					choice=13;
			}
			else if(choice+level*14==titles.size()-1)
				;
			else
				++choice;
			while(GetAsyncKeyState(VK_DOWN))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}
		
		if(GetAsyncKeyState(VK_UP))
		{
			if(choice==0)
			{
				if(level==0)
					;
				else
				{
					--level;
					choice=13;
				}
			}
			else 
				--choice;
			while(GetAsyncKeyState(VK_UP))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_RIGHT))
		{
			if((level+1)*14<titles.size())
			{
				++level;
				choice=0;
			}
			while(GetAsyncKeyState(VK_RIGHT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_LEFT))
		{
			if(level>0)
			{
				--level;
				choice=13;
			}
			else
				;
			while(GetAsyncKeyState(VK_LEFT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}



	}while(!GetAsyncKeyState(VK_RETURN));
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
	int id;
	GetSongID(retvalue, hdbc1, hstmt1, titles[choice+level*14], id);
	Song sng;
	sng.id = id;
	sng.title=titles[choice+level*14];
	presentation.songs.push_back(sng);

}

void ViewEditPresentation(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Presentation& presentation, int selectColor)
{
	system("cls");
	cout << "Press RETURN to go back or X to delete the selected song.\n"
		<< "UP and DOWN to select songs; LEFT and RIGHT to fast scroll.";
	if(!presentation.songs.empty())
	{
		unsigned int choice = 0, oldchoice=0, level=0, oldlevel=0;
		bool erase;
		for(unsigned int i=0; i<14 && i<presentation.songs.size(); ++i)
		{
			gotoxy(0,4+4*i);
			if(i==0)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << presentation.songs[i].title;
			if(i==0)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		}
		do
		{
		
			if(choice!=oldchoice || oldlevel!=level)
			{
				if(oldlevel==level)
				{
					gotoxy(0, 4+4*oldchoice);
					cout << presentation.songs[oldchoice+level*14].title;
				}
				else
				{
					for(unsigned int i=0; i<14; ++i)
					{
						gotoxy(0, 4+4*i);
						for(unsigned int j=0; j<80; ++j)
							cout << " ";
					}
					for(unsigned int i=0; i<14 && i+level*14<presentation.songs.size(); ++i)
					{
						gotoxy(0,4+4*i);
						cout << presentation.songs[i+level*14].title;
					}
				}
				gotoxy(0, 4+4*choice);
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				cout << presentation.songs[choice+level*14].title;
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
			}
			oldchoice=choice;
			oldlevel=level;
			erase=false;

			if(GetAsyncKeyState(VK_DOWN))
			{
				if(choice == 13)
				{
					if((level+1)*14 < presentation.songs.size())
					{
						++level;
						choice=0;
					}
					else 
						choice=13;
				}
				else if(choice+level*14==presentation.songs.size()-1)
					;
				else
					++choice;
				while(GetAsyncKeyState(VK_DOWN))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}
		
			if(GetAsyncKeyState(VK_UP))
			{
				if(choice==0)
				{
					if(level==0)
						;
					else
					{
						--level;
						choice=13;
					}
				}
				else 
					--choice;
				while(GetAsyncKeyState(VK_UP))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(VK_RIGHT))
			{
				if((level+1)*14<presentation.songs.size())
				{
					++level;
					choice=0;
				}
				while(GetAsyncKeyState(VK_RIGHT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(VK_LEFT))
			{
				if(level>0)
				{
					--level;
					choice=13;
				}
				else
					;
				while(GetAsyncKeyState(VK_LEFT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(0x58))
			{
				presentation.songs.erase(presentation.songs.begin()+choice+level*14);
				if(choice+level*14==0 && choice+level*14==presentation.songs.size())
					break;
				if(choice+level*14==presentation.songs.size())
				{
					if(choice==0)
					{
						--level;
						choice=13;
						oldchoice=13;
					}
					else
					{
						--choice;
						--oldchoice;
					}
				}
				for(unsigned int i=choice; i<14; ++i)
				{
					gotoxy(0, 4+4*i);
					for(unsigned int j=0; j<80; ++j)
						cout << " ";
				}

				for(unsigned int i=choice; i<14 && i+level*14<presentation.songs.size(); ++i)
				{
					gotoxy(0,4+4*i);
					if(i==choice)
						SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
					cout << presentation.songs[i+level*14].title;
					if(i==choice)
						SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
				}

				erase=true;
				while(GetAsyncKeyState(0x58))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}


		}while(!GetAsyncKeyState(VK_RETURN));
		while(GetAsyncKeyState(VK_RETURN))
			;
		FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
	}

	if(presentation.songs.empty())
	{
		system("cls");
		cout << "I'm sorry, the presentation currently contains no songs.\n"
			<< "Press RETURN to go back.";
		while(!GetAsyncKeyState(VK_RETURN))
			;
	}

	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));


}

void GetStanzaInfoForStanza(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, int songid, int index_in_song, StanzaInfo& info)
{
	stringstream ss;
	ss << "SELECT stanza_type, stanza_nr FROM song_has_stanza WHERE song_id = " << songid << " AND index_in_song = " << index_in_song;
	allocate_handle(retvalue, hdbc1, hstmt1);

	SQLINTEGER name_length;
	SQLSMALLINT stanza_type, stanza_nr;

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"GetIndexInSong\"" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_SHORT, &stanza_type, sizeof(stanza_type), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 2, SQL_C_SHORT, &stanza_nr, sizeof(stanza_nr), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 2" << endl;
		exit(1);
	}


	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		info.stanza_type = stanza_type;
		info.stanza_nr = stanza_nr;
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void ChooseStanzaType(StanzaInfo& info, int selectColor)
{
	system("cls");
	string str = "Please select a stanza type for the created stanza.";
	gotoxy((80-str.length())/2, 0);
	cout << str;
	unsigned int oldchoice=1, choice=0;
	do
	{
			
		if(oldchoice!=choice)
		{
			if(choice==0)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			gotoxy(5,2);
			cout << "Verse";
			if(choice==0)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

			if(choice==1)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			gotoxy(25,2);
			cout << "Chorus";
			if(choice==1)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

			if(choice==2)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			gotoxy(45,2);
			cout << "Bridge";
			if(choice==2)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

			if(choice==3)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			gotoxy(65,2);
			cout << "Misc";
			if(choice==3)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		}
		oldchoice=choice;

		if(GetAsyncKeyState(VK_RIGHT))
		{
			choice = (choice+1)%4;
			while(GetAsyncKeyState(VK_RIGHT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_LEFT))
		{
			if(choice==0)
				choice=3;
			else
				--choice;
			while(GetAsyncKeyState(VK_LEFT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

	}while(!GetAsyncKeyState(VK_RETURN));
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));

	info.stanza_type=choice+1;
}

void EnterComment(string& str)
{
	char temp[1001];
	string temps;
	stringstream ss;

	system("cls");
	cout << "If so desired, please enter a comment. (1000 character max)\n"
		<< "Press RETURN to continue.\n\n";
	cin.getline(temp, 1000);
	ss << temp;
	temps = ss.str();
	ss.str(string());
	ss.clear();
	for(unsigned int i=0; i<temps.size(); ++i)
	{
		ss << temp[i];
		if(temp[i]=='\'')
			ss << '\'';
	}
	str=ss.str();
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
}

void InsertSong(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Song& song)
{
	InsertIntoSong(retvalue, hdbc1, hstmt1, song);
	GetSongID(retvalue, hdbc1, hstmt1, song.title, song.id);
	for(int i = 0; i<song.stanzasinfo.size(); ++i)
	{
		InsertIntoStanza(retvalue, hdbc1, hstmt1, song, i);
		InsertIntoStanza_Has_Line(retvalue, hdbc1, hstmt1, song, i);
		InsertIntoSong_Has_Stanza(retvalue, hdbc1, hstmt1, song, i);
		
		
	}

}

void InsertIntoSong(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Song song)
{
	stringstream ss;
	string title;
	for(unsigned int i=0; i<song.title.size(); ++i)
	{
		ss << song.title[i];
		if(song.title[i]=='\'')
			ss << '\'';
	}
	title = ss.str();
	ss.str(string());
	ss.clear();

	ss << "INSERT INTO song VALUES ('" << title << "', '" << song.comment << "');";
	allocate_handle(retvalue, hdbc1, hstmt1);


	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"InsertIntoSong\"" << endl;
		exit(1);
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);

}

void InsertIntoStanza(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Song song, int i)
{
	stringstream ss;
	ss << "INSERT INTO stanza VALUES (" << song.id << ", " << song.stanzasinfo[i].stanza_type << ", " << song.stanzasinfo[i].stanza_nr << ");";
	allocate_handle(retvalue, hdbc1, hstmt1);

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"InsertIntoStanza\"" << endl;
		exit(1);
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void InsertIntoStanza_Has_Line(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Song song, int i)
{
	for(int j = 0; j<song.stanzas[i].size();++j)
	{
		stringstream ss;
		string line;
		for(int k = 0; k < song.stanzas[i][j].length(); ++k)
		{
			ss << song.stanzas[i][j][k];
			if(song.stanzas[i][j][k]=='\'')
				ss << '\'';
		}
		line = ss.str();
		ss.str(string());
		ss.clear();
		ss << "INSERT INTO stanza_has_line VALUES (" << song.id << ", " << song.stanzasinfo[i].stanza_type << ", " << song.stanzasinfo[i].stanza_nr 
			<< ", " << j+1 << ", '" << line << "');";
		allocate_handle(retvalue, hdbc1, hstmt1);

		retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
		if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
		{
			cerr << "Failed to execute \"InsertIntoStanza_Has_Line\"" << endl;
			exit(1);
		}

		deallocate_handle(retvalue, hdbc1, hstmt1);
		ss.str(string());
		ss.clear();
	}
}

void InsertIntoSong_Has_Stanza(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Song song, int i)
{
	stringstream ss;
	ss << "INSERT INTO song_has_stanza VALUES (" << song.id << ", " << i+1 << ", " << song.stanzasinfo[i].stanza_type 
		<< ", " << song.stanzasinfo[i].stanza_nr << ");";
	allocate_handle(retvalue, hdbc1, hstmt1);

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"InsertIntoSong_Has_Stanza\"" << endl;
		exit(1);
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void InsertPresentation(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Presentation& presentation)
{
	InsertIntoPresentation(retvalue, hdbc1, hstmt1, presentation);
	InsertIntoPresentation_Has_Song(retvalue, hdbc1, hstmt1, presentation);
}

void ChooseDateCreatedFor(Presentation& presentation, int selectColor)
{
	system("cls");
	cout << "Select the Month this is created for.\n";

	unsigned int choice=0, oldchoice=1;

	gotoxy(0,2);
	SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
	cout << "1";
	SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
	gotoxy(7, 2);
	cout << "2";
	gotoxy(14,2);
	cout << "3";
	gotoxy(21,2);
	cout << "4";
	gotoxy(28,2);
	cout << "5";
	gotoxy(35,2);
	cout << "6";
	gotoxy(42, 2);
	cout << "7";
	gotoxy(49,2);
	cout << "8";
	gotoxy(56,2);
	cout << "9";
	gotoxy(63,2);
	cout << "10";
	gotoxy(70,2);
	cout << "11";
	gotoxy(77,2);
	cout << "12";

	do
	{
		
		if(oldchoice!=choice)
		{
			gotoxy(7*oldchoice,2);
			cout << oldchoice+1;
			gotoxy(7*choice,2);
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << choice+1;
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		}
		oldchoice=choice;

		if(GetAsyncKeyState(VK_RIGHT))
		{
			choice = (choice+1)%12;
			while(GetAsyncKeyState(VK_RIGHT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_LEFT))
		{
			if(choice==0)
				choice=11;
			else
				--choice;
			while(GetAsyncKeyState(VK_LEFT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

	}while(!GetAsyncKeyState(VK_RETURN));
	while(GetAsyncKeyState(VK_RETURN))
		;
	presentation.dateCreatedFor.month=choice+1;

	system("cls");
	cout << "Select the Day this is created for.";
	int limit;
	oldchoice=1;
	choice=0;
	if(choice+1==2)
		limit=29;
	else if(choice+1==4 || choice+1==6 || choice+1==9 || choice+1==11)
		limit=30;
	else
		limit=31;
	for(int i = 0; i<limit; ++i)
	{
		gotoxy((i%7)*10, (i/7)+3);
		/*if(i==0)
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);*/
		cout << i+1;
		/*if(i==0)
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);*/
	}
	do
	{
		
		if(oldchoice!=choice)
		{
			gotoxy((oldchoice%7)*10,(oldchoice/7)+3);
			cout << oldchoice+1;
			gotoxy((choice%7)*10,(choice/7) + 3);
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << choice+1;
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		}
		oldchoice=choice;

		if(GetAsyncKeyState(VK_RIGHT))
		{
			choice = (choice+1)%limit;
			while(GetAsyncKeyState(VK_RIGHT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_LEFT))
		{
			if(choice==0)
				choice=limit-1;
			else
				--choice;
			while(GetAsyncKeyState(VK_LEFT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

	}while(!GetAsyncKeyState(VK_RETURN));
	while(GetAsyncKeyState(VK_RETURN))
		;

	presentation.dateCreatedFor.day=choice+1;

	system("cls");
	oldchoice=1;
	choice=0;

	do
	{
		
		if(oldchoice!=choice)
		{
			gotoxy(10, 3);
			if(choice==0)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << "2011";
			if(choice==0)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
			gotoxy(30,3);
			if(choice==1)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << "2012";
			if(choice==1)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
			gotoxy(50,3);
			if(choice==2)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << "2013";
			if(choice==2)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);

		}
		oldchoice=choice;

		if(GetAsyncKeyState(VK_RIGHT))
		{
			choice = (choice+1)%13;
			while(GetAsyncKeyState(VK_RIGHT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_LEFT))
		{
			if(choice==0)
				choice=2;
			else
				--choice;
			while(GetAsyncKeyState(VK_LEFT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

	}while(!GetAsyncKeyState(VK_RETURN));
	while(GetAsyncKeyState(VK_RETURN))
		;

	presentation.dateCreatedFor.year=2011+choice;
}

void InsertIntoPresentation(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Presentation presentation)
{
	stringstream ss;
	string title = presentation.title, author=presentation.author;
	for(unsigned int i=0; i<title.size(); ++i)
	{
		ss << title[i];
		if(title[i]=='\'')
			ss << '\'';
	}
	title = ss.str();
	ss.str(string());
	ss.clear();
	for(unsigned int i=0; i<author.size(); ++i)
	{
		ss << author[i];
		if(author[i]=='\'')
			ss << '\'';
	}
	author = ss.str();
	ss.str(string());
	ss.clear();
	ss << "INSERT INTO presentation VALUES ('" << title << "', '" << author << "', '"
		<< presentation.dateCreatedFor.year << "-" << presentation.dateCreatedFor.month << "-" << presentation.dateCreatedFor.day 
		<< "', GETDATE(), NULL, GETDATE(), '" << presentation.comment << "');";
	allocate_handle(retvalue, hdbc1, hstmt1);


	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"InsertIntoPresentation\"" << endl;
		exit(1);
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void InsertIntoPresentation_Has_Song(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, Presentation presentation)
{
	stringstream ss;
	string title = presentation.title, author=presentation.author;
	for(unsigned int i=0; i<title.size(); ++i)
	{
		ss << title[i];
		if(title[i]=='\'')
			ss << '\'';
	}
	title = ss.str();
	ss.str(string());
	ss.clear();
	for(unsigned int i=0; i<author.size(); ++i)
	{
		ss << author[i];
		if(author[i]=='\'')
			ss << '\'';
	}
	author = ss.str();
	ss.str(string());
	ss.clear();
	for(int j = 0; j < presentation.songs.size(); ++j)
	{
		ss << "INSERT INTO presentation_has_song VALUES ('" << title << "', '" << author << "', "
			<< j+1 << ", " << presentation.songs[j].id << ");";
		allocate_handle(retvalue, hdbc1, hstmt1);


		retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
		if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
		{
			cerr << "Failed to execute \"InsertIntoPresentation_Has_Song\"" << endl;
			exit(1);
		}

		deallocate_handle(retvalue, hdbc1, hstmt1);

		ss.str(string());
		ss.clear();
	}
}

void DeletePresentation(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, int selectColor)
{
	system("cls");
	vector<string> titles;
	GetPresentationTitles(retvalue, hdbc1, hstmt1, titles);

	system("cls");
	cout << "Select the title of the presentation that you wish to delete."
		<< "\n\tUP and DOWN for selecting; LEFT and RIGHT for fast scrolling; BACKSPACE to cancel";
	unsigned int choice = 0, oldchoice=0, level=0, oldlevel=0;
	for(unsigned int i=0; i<14 && i<titles.size(); ++i)
	{
		gotoxy(0,4+4*i);
		if(i==0)
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
		cout << titles[i];
		if(i==0)
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
	}
	do
	{
		
		if(choice!=oldchoice || oldlevel!=level)
		{
			if(oldlevel==level)
			{
				gotoxy(0, 4+4*oldchoice);
				cout << titles[oldchoice+level*14];
			}
			else
			{
				for(unsigned int i=0; i<14; ++i)
				{
					gotoxy(0, 4+4*i);
					for(unsigned int j=0; j<80; ++j)
						cout << " ";
				}
				for(unsigned int i=0; i<14 && i+level*14<titles.size(); ++i)
				{
					gotoxy(0,4+4*i);
					cout << titles[i+level*14];
				}
			}
			gotoxy(0, 4+4*choice);
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << titles[choice+level*14];
			SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		}
		oldchoice=choice;
		oldlevel=level;

		if(GetAsyncKeyState(VK_DOWN))
		{
			if(choice == 13)
			{
				if((level+1)*14 < titles.size())
				{
					++level;
					choice=0;
				}
				else 
					choice=13;
			}
			else if(choice+level*14==titles.size()-1)
				;
			else
				++choice;
			while(GetAsyncKeyState(VK_DOWN))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}
		
		if(GetAsyncKeyState(VK_UP))
		{
			if(choice==0)
			{
				if(level==0)
					;
				else
				{
					--level;
					choice=13;
				}
			}
			else 
				--choice;
			while(GetAsyncKeyState(VK_UP))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_RIGHT))
		{
			if((level+1)*14<titles.size())
			{
				++level;
				choice=0;
			}
			while(GetAsyncKeyState(VK_RIGHT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_LEFT))
		{
			if(level>0)
			{
				--level;
				choice=13;
			}
			else
				;
			while(GetAsyncKeyState(VK_LEFT))
				;
			FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
		}

		if(GetAsyncKeyState(VK_BACK))
		{
			while(GetAsyncKeyState(VK_BACK))
				;
			return;
		}

	}while(!GetAsyncKeyState(VK_RETURN));
	while(GetAsyncKeyState(VK_RETURN))
		;
	FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
	
	string title = titles[choice], author;

	vector<string> authors;
	GetPresentationAuthors(retvalue, hdbc1, hstmt1, title, authors);

	system("cls");
	if(authors.size()>1)
	{
		system("cls");
		cout << "Select the author of the presentation that you wish to delete."
			<< "\n\tUP and DOWN for selecting; LEFT and RIGHT for fast scrolling; BACKSPACE to cancel";
		unsigned int choice = 0, oldchoice=0, level=0, oldlevel=0;
		for(unsigned int i=0; i<14 && i<authors.size(); ++i)
		{
			gotoxy(0,4+4*i);
			if(i==0)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
			cout << authors[i];
			if(i==0)
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
		}
		do
		{
		
			if(choice!=oldchoice || oldlevel!=level)
			{
				if(oldlevel==level)
				{
					gotoxy(0, 4+4*oldchoice);
					cout << authors[oldchoice+level*14];
				}
				else
				{
					for(unsigned int i=0; i<14; ++i)
					{
						gotoxy(0, 4+4*i);
						for(unsigned int j=0; j<80; ++j)
							cout << " ";
					}
					for(unsigned int i=0; i<14 && i+level*14<authors.size(); ++i)
					{
						gotoxy(0,4+4*i);
						cout << authors[i+level*14];
					}
				}
				gotoxy(0, 4+4*choice);
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				cout << authors[choice+level*14];
				SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
			}
			oldchoice=choice;
			oldlevel=level;

			if(GetAsyncKeyState(VK_DOWN))
			{
				if(choice == 13)
				{
					if((level+1)*14 < authors.size())
					{
						++level;
						choice=0;
					}
					else 
						choice=13;
				}
				else if(choice+level*14==authors.size()-1)
					;
				else
					++choice;
				while(GetAsyncKeyState(VK_DOWN))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}
		
			if(GetAsyncKeyState(VK_UP))
			{
				if(choice==0)
				{
					if(level==0)
						;
					else
					{
						--level;
						choice=13;
					}
				}
				else 
					--choice;
				while(GetAsyncKeyState(VK_UP))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(VK_RIGHT))
			{
				if((level+1)*14<authors.size())
				{
					++level;
					choice=0;
				}
				while(GetAsyncKeyState(VK_RIGHT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(VK_LEFT))
			{
				if(level>0)
				{
					--level;
					choice=13;
				}
				else
					;
				while(GetAsyncKeyState(VK_LEFT))
					;
				FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));
			}

			if(GetAsyncKeyState(VK_BACK))
			{
				while(GetAsyncKeyState(VK_BACK))
					;
				return;
			}

		}while(!GetAsyncKeyState(VK_RETURN));
		while(GetAsyncKeyState(VK_RETURN))
			;
		FlushConsoleInputBuffer(GetStdHandle(STD_INPUT_HANDLE));

		author=authors[choice];
	}else
		author=authors[0];

	DeleteFromPresentation_Has_Song(retvalue, hdbc1, hstmt1, title, author);
	DeleteFromPresentation(retvalue, hdbc1, hstmt1, title, author);

}

void GetPresentationTitles(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, vector<string>& titles)
{
	SQLINTEGER name_length;
	SQLCHAR title[21];
	stringstream ss;

	allocate_handle(retvalue, hdbc1, hstmt1);

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)"SELECT DISTINCT presentation_title FROM presentation ORDER BY presentation_title ASC", SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"GetPresentationTitles\"" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &title, sizeof(title), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		ss << title;
		titles.push_back(ss.str());
		ss.str(string());
		ss.clear();
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void GetPresTitles(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, string author, vector<string>& titles)
{
	stringstream ss;
	for(unsigned int i=0; i<author.size(); ++i)
	{
		ss << author[i];
		if(author[i]=='\'')
			ss << '\'';
	}
	author = ss.str();
	ss.str(string());
	ss.clear();

	SQLINTEGER name_length;
	SQLCHAR title[21];
	

	allocate_handle(retvalue, hdbc1, hstmt1);

	ss << "SELECT presentation_title FROM presentation WHERE author='" << author << "' ORDER BY presentation_title ASC";

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"GetPresentationTitles\"" << endl;
		exit(1);
	}

	ss.str(string());
	ss.clear();

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &title, sizeof(title), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		ss << title;
		titles.push_back(ss.str());
		ss.str(string());
		ss.clear();
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void GetPresentationAuthors(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, string title, vector<string>& authors)
{
	stringstream ss;
	for(unsigned int i=0; i<title.size(); ++i)
	{
		ss << title[i];
		if(title[i]=='\'')
			ss << '\'';
	}
	title = ss.str();
	ss.str(string());
	ss.clear();

	SQLINTEGER name_length;
	SQLCHAR author[41];
	

	allocate_handle(retvalue, hdbc1, hstmt1);

	ss << "SELECT author FROM presentation WHERE presentation_title='" << title << "' ORDER BY author ASC";

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"GetPresentationTitles\"" << endl;
		exit(1);
	}

	ss.str(string());
	ss.clear();

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &author, sizeof(author), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		ss << author;
		authors.push_back(ss.str());
		ss.str(string());
		ss.clear();
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void GetPresAuthors(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, vector<string>& authors)
{
	allocate_handle(retvalue, hdbc1, hstmt1);
	stringstream ss;
	ss << "SELECT author FROM presentation ORDER BY author ASC";

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"GetPresentationTitles\"" << endl;
		exit(1);
	}

	SQLINTEGER name_length;
	SQLCHAR author[41];

	ss.str(string());
	ss.clear();

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &author, sizeof(author), &name_length);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		ss << author;
		authors.push_back(ss.str());
		ss.str(string());
		ss.clear();
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void DeleteFromPresentation(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, string title, string author)
{
	stringstream ss;
	for(unsigned int i=0; i<title.size(); ++i)
	{
		ss << title[i];
		if(title[i]=='\'')
			ss << '\'';
	}
	title = ss.str();
	ss.str(string());
	ss.clear();

	for(unsigned int i=0; i<author.size(); ++i)
	{
		ss << author[i];
		if(author[i]=='\'')
			ss << '\'';
	}
	author = ss.str();
	ss.str(string());
	ss.clear();
	

	allocate_handle(retvalue, hdbc1, hstmt1);

	ss << "DELETE FROM presentation WHERE presentation_title='"
		<< title << "' AND author='" << author << "'";

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"DeleteFromPresentation\"" << endl;
		exit(1);
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);
}

void DeleteFromPresentation_Has_Song(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1, string title, string author)
{
	stringstream ss;
	for(unsigned int i=0; i<title.size(); ++i)
	{
		ss << title[i];
		if(title[i]=='\'')
			ss << '\'';
	}
	title = ss.str();
	ss.str(string());
	ss.clear();

	for(unsigned int i=0; i<author.size(); ++i)
	{
		ss << author[i];
		if(author[i]=='\'')
			ss << '\'';
	}
	author = ss.str();
	ss.str(string());
	ss.clear();


	allocate_handle(retvalue, hdbc1, hstmt1);

	ss << "DELETE FROM presentation_has_song WHERE presentation_title='"
		<< title << "' AND author='" << author << "'";

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"DeleteFromPresentation_Has_Song\"" << endl;
		exit(1);
	}

	deallocate_handle(retvalue, hdbc1, hstmt1);

}